<?php
class Line_base_model extends CI_Model
{
    private $table   = 'line_base';
    private $table_2 = 'line_travel_type';
    private $table_3 = 'user_detail';
    private $table_4 = 'line_price';
    private $table_5 = 'line_product_type';
    private $table_6 = 'user';
    private $table_7 = 'line_related_destination';
    private $table_8 = 'line_destination';
    

    public function findViewById($id)
    {
        $this->db->select('line_base.*, user.uid, user.user_name, user.alias_name, user.user_type');
        $this->db->from($this->table.' as line_base');
        $this->db->join($this->table_6.' as user', 'user.uid=line_base.uid');
        $this->db->where('line_base.id', $id); //产品id
        $this->db->where('line_base.status', 1); //上架产品
        $this->db->where('line_base.audit_status', 2); //审核成功产品
        return $this->db->get();
    }
    
    /**
     * 预览时用的
     * @param unknown $id
     */
    public function findByReviewId($id)
    {
        $this->db->select('line_base.*, user.uid, user.user_name, user.alias_name, user.user_type');
        $this->db->from($this->table.' as line_base');
        $this->db->join($this->table_6.' as user', 'user.uid=line_base.uid');
        $this->db->where('line_base.id', $id); //产品id
        return $this->db->get();
    }
    
    //列表页sql
    public function findCatalogTotal($getData)
    {
        $sql = 'SELECT count(*) AS total
                FROM (`line_base` AS line_base)
                WHERE line_base.status=1 AND line_base.audit_status=2';
        if (!empty($getData['address'])) {
            $address = trim(addslashes($getData['address']));
            $destinc = "SELECT `line_id` AS id
                FROM `line_related_destination` INNER JOIN `line_destination` ON line_related_destination.destination_id = line_destination.destination_id
                WHERE address LIKE '%".$address."%'";
            $sql = $sql.' AND (`id` IN ('.$destinc.') OR `line_base`.`line_name` LIKE "%'.$address.'%")';
        }
        /*
        if (!empty($getData['start_address'])) {
            $sql = $sql.' AND `line_base`.`start_address`="'.$getData['start_address'].'"';
        }
        */
        if (!empty($getData['travel_days'])) {
            $sql = $sql.' AND `line_base`.`travel_days`='.intval($getData['travel_days']);
        }
        if (!empty($getData['line_product_type'])) {
            $sql = $sql.' AND `line_base`.`line_product_type`='.intval($getData['line_product_type']);
        }
        if (!empty($getData['line_travel_type'])) {
            $sql = $sql.' AND `line_base`.`line_travel_type`='.intval($getData['line_travel_type']);
        }
        if(!empty($getData['price_range'])){
            $sql = $sql.($this->getPriceSql($getData['price_range']));
        }
        $result = $this->db->query($sql);
        if ($result->num_rows() > 0) {
            return $result->row()->total;
        }
        return 0;
    }
    
    public function findCatalogPageList($pageNum, $num, $getData)
    {
        $sql = 'SELECT * 
                FROM (`line_base` AS line_base)
                WHERE line_base.status=1 AND line_base.audit_status=2';
        if (!empty($getData['address'])) {
            $address = trim(addslashes($getData['address']));
            $destinc = "SELECT `line_id` AS id
                FROM `line_related_destination` INNER JOIN `line_destination` ON line_related_destination.destination_id = line_destination.destination_id
                WHERE address LIKE '%".$address."%'";
            $sql = $sql.' AND (`id` IN ('.$destinc.') OR `line_base`.`line_name` LIKE "%'.$address.'%")';
        }
        /*
        if (!empty($getData['start_address'])) {
            $sql = $sql.' AND `line_base`.`start_address`="'.$getData['start_address'].'"';
        }
        */
        if (!empty($getData['travel_days'])) {
            $sql = $sql.' AND `line_base`.`travel_days`='.intval($getData['travel_days']);
        }
        if (!empty($getData['line_product_type'])) {
            $sql = $sql.' AND `line_base`.`line_product_type`='.intval($getData['line_product_type']);
        }
        if (!empty($getData['line_travel_type'])) {
            $sql = $sql.' AND `line_base`.`line_travel_type`='.intval($getData['line_travel_type']);
        }
        if(!empty($getData['price_range'])){
            $sql = $sql.($this->getPriceSql($getData['price_range']));
        }
        $sql = $sql.' ORDER BY `line_base`.`homepage` ASC LIMIT '.$num.', '.$pageNum;
        return $this->db->query($sql);
    }
    
    public function findCatalogPageList1($getData)
    {
        $sql = 'SELECT * 
                FROM (`line_base` AS line_base)
                WHERE line_base.status=1 AND line_base.audit_status=2';
        if (!empty($getData['address'])) {
            $address = trim(addslashes($getData['address']));
            $destinc = "SELECT `line_id` AS id
                FROM `line_related_destination` INNER JOIN `line_destination` ON line_related_destination.destination_id = line_destination.destination_id
                WHERE address LIKE '%".$address."%'";
            $sql = $sql.' AND (`id` IN ('.$destinc.') OR `line_base`.`line_name` LIKE "%'.$address.'%")';
        }
        /*
        if (!empty($getData['start_address'])) {
            $sql = $sql.' AND `line_base`.`start_address`="'.$getData['start_address'].'"';
        }
        */
        if (!empty($getData['travel_days'])) {
            $sql = $sql.' AND `line_base`.`travel_days`='.intval($getData['travel_days']);
        }
        if (!empty($getData['line_product_type'])) {
            $sql = $sql.' AND `line_base`.`line_product_type`='.intval($getData['line_product_type']);
        }
        if (!empty($getData['line_travel_type'])) {
            $sql = $sql.' AND `line_base`.`line_travel_type`='.intval($getData['line_travel_type']);
        }
        
        return $this->db->query($sql);
    }
    
    /**
     * 线路列表页展示数据
     * @param array $line_travel_type 线路类型id
     * @param int $limit
     * @return resource
     */
    public function hotProductTop10($line_travel_type, $limit)
    {
        $this->db->select('*');
        $this->db->from($this->table);
        if (!empty($line_travel_type)) {
            $this->db->where('line_travel_type', $line_travel_type);
        }
        $this->db->where('status', 1); //上架产品
        $this->db->where('audit_status', 2); //审核成功产品
        $this->db->order_by('sales_qty', 'DESC');
        $this->db->limit($limit);
        return $this->db->get();
    }
    
    public function lineHomePageChoice($line_travel_type, $limit)
    {
        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('line_travel_type', $line_travel_type);
        $this->db->where('status', 1); //上架产品
        $this->db->where('audit_status', 2); //审核成功产品
        $this->db->order_by('homepage', 'ASC');
        $this->db->limit($limit);
        return $this->db->get();
    }
    
    public function findByRecommend($line_travel_type, $limit)
    {
        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('line_travel_type', $line_travel_type);
        $this->db->where('status', 1); //上架产品
        $this->db->where('audit_status', 2); //审核成功产品
        $this->db->order_by('updated_at', 'DESC');
        $this->db->limit($limit);
        return $this->db->get();
    }
    
    /**
     * wap线路导航展示数据
     * @param array $line_travel_type 线路类型id
     * @param int $limit
     * @param string $city
     * @return resource
     */
    
    public function findDhLineMark($city,$line_travel_type) 
    {
       $this->db->select('id');
       $this->db->from($this->table);
       $this->db->where('status',1); //上架产品
       $this->db->where('audit_status', 2); //审核成功产品
       if(!empty($city)){
       	  $this->db->where('start_address',$city);
       }
       if(!empty($line_travel_type)){
          $this->db->where('line_travel_type',$line_travel_type);
       }
       $this->db->order_by('updated_at', 'DESC');
       return $this->db->get();
    }
    
    /**
     *根据id 只查询line_base 这张表 
     */
    public function findBylineId($line_id){
    	
       $this->db->where('id',$line_id);
       return $this->db->get($this->table);
    }

   /**
    * 价格sql语句
    * @param unknown $price_range  
    * @return string
    */
    public function getPriceSql($price_range){
    	
        switch ($price_range){
        	case '1': $price_sql = ' AND `line_base`.`bz_price` BETWEEN 0 AND 100'; break;
        	case '2': $price_sql = ' AND `line_base`.`bz_price` BETWEEN 101 AND 300'; break;
        	case '3': $price_sql = ' AND `line_base`.`bz_price` BETWEEN 301 AND 800'; break;
        	case '4': $price_sql = ' AND `line_base`.`bz_price` BETWEEN 801 AND 2000'; break;
        	case '5': $price_sql = ' AND `line_base`.`bz_price` BETWEEN 2001 AND 6000'; break;
        	case '6': $price_sql = ' AND `line_base`.`bz_price` BETWEEN 6001 AND 10000'; break;
        	case '7': $price_sql = ' AND `line_base`.`bz_price` > 10000'; break;
        }
        return $price_sql;
    }
    
    public function updateSalesQty($id,$sales_qty)
    {
        $this->db->set('sales_qty', 'sales_qty+'.$sales_qty, false);
        $this->db->where('id', $id);
        return $this->db->update($this->table);
    }
    
      /**
     * 通过供应商uid查找线路
     * @param unknown $uid
     * @param unknown $page_num
     * @param unknown $num
     */
    public function getLineBaseByUid($uid, $line_id, $page_num, $num)
    {
    	$this->db->select('line_base.id,line_base.line_name,line_base.line_image,line_base.original_price,line_base.bz_price,line_base.start_address');
    	$this->db->from($this->table . ' as line_base');
    	$this->db->join($this->table_6 . ' as user', 'user.uid=line_base.uid');
    	$this->db->where('line_base.audit_status', 2); //审核通过的产品
    	$this->db->where('line_base.status', 1); //上架产品
    	$this->db->where('user.uid', $uid);
    	$this->db->where('user.flag', 1);//没有冻结
    	if (!empty($line_id)) {
    		$this->db->where_not_in('line_base.id', $line_id);
    	}
    	$this->db->limit($page_num, $num);
    	$this->db->order_by('homepage', 'asc');// 升序
    	return $this->db->get();
    }

    /**
     * 2016 11.1 新首页 自在游 城市 线路列表
     * @param $city
     * @param $page_num
     * @param $num
     * @return mixed
     */
    public function findLineList($city_id, $page_num, $num)
    {
        $this->db->select('line_base.id, line_base.line_name, line_base.line_travel_type, line_base.line_image, line_base.bz_price, line_base.sales_qty,line_base.start_address');
        $this->db->from($this->table);
        $this->db->join($this->table_7 . ' as line_related_destination', 'line_related_destination.line_id=line_base.id');
        $this->db->join($this->table_8 . ' as line_destination', 'line_destination.destination_id=line_related_destination.destination_id');
        $this->db->where('status',1); //上架产品
        $this->db->where('audit_status', 2); //审核成功产品
        if(!empty($city_id)){
            $this->db->where('line_destination.city_id',$city_id);
        }
        $this->db->limit($page_num, $num);
//        $this->db->order_by('updated_at', 'DESC');
        return $this->db->get();
    }
    
}